using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Taxhui.Utils.UserModel;

namespace Taxhui.Utils.DBUtils.SQLDBUtil
{    
    public class DB
    {
        private readonly SqlDBConfig Config = null;
        private SqlConnection DBConn = null;
        public DB(SqlDBConfig cfg)
        {
            try
            {
                Config = cfg ?? throw new Exception("配置不能为空");
            }
            catch (Exception)
            {
                throw;
            }
        }
        private void OpenDB()
        {
            try
            {
                if (null == DBConn)
                {
                    DBConn = new SqlConnection(Config.ConnStr);
                    DBConn.Open();
                    return;
                }
                if (DBConn.State != ConnectionState.Open)
                {
                    DBConn = new SqlConnection(Config.ConnStr);
                    DBConn.Open();
                    return;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        private void CloseDB()
        {
            try
            {
                DBConn.Close();
                DBConn.Dispose();
                DBConn = null;
            }
            catch (Exception)
            {
            }
        }
        public DataTable Query(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                {
                    throw new Exception("命令不能为空！");
                }
                OpenDB();
                SqlDataAdapter da = new SqlDataAdapter(sqlstr, DBConn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                CloseDB();
                da.Dispose();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable Query(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                {
                    throw new Exception("命令不能为空！");
                }
                if (null == op)
                {
                    throw new Exception("参数不能为空！");
                }
                if (op.Length < 1)
                {
                    throw new Exception("参数不能为空！");
                }
                CheckParameterNull(op);
                OpenDB();
                SqlDataAdapter da = new SqlDataAdapter(sqlstr, DBConn);
                da.SelectCommand.Parameters.AddRange(op);
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.SelectCommand.Parameters.Clear();
                da.Dispose();
                da = null;
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void Exec(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                {
                    throw new Exception("命令不能为空！");
                }
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, DBConn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void Exec(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == op)
                    throw new Exception("参数不能为空！");
                CheckParameterNull(op);
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, DBConn);
                cmd.Parameters.AddRange(op);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecTrans(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand
                {
                    Connection = DBConn,
                    CommandType = CommandType.StoredProcedure,
                    CommandText = sqlstr
                };
                cmd.ExecuteNonQuery();
                cmd.Transaction.Commit();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecTrans(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == op)
                    throw new Exception("参数不能为空！");
                if (op.Length < 1)
                    throw new Exception("参数不能为空！");
                CheckParameterNull(op);
                OpenDB();
                SqlCommand cmd = new SqlCommand
                {
                    Connection = DBConn,
                    CommandText = sqlstr,
                    CommandType = CommandType.StoredProcedure
                };
                cmd.Parameters.AddRange(op);
                cmd.ExecuteNonQuery();
                cmd.Transaction.Commit();
                cmd.Dispose();
                cmd = null;
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable QueryTrans(string sqlstr)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand
                {
                    CommandText = sqlstr,
                    CommandType = CommandType.StoredProcedure,
                    Connection = DBConn
                };
                cmd.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter
                {
                    SelectCommand = cmd
                };
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                da = null;
                cmd.Dispose();
                cmd = null;
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable QueryTrans(string sqlstr, SqlParameter[] op)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == op)
                    throw new Exception("参数不能为空！");
                if (op.Length < 1)
                    throw new Exception("参数不能为空！");
                CheckParameterNull(op);
                OpenDB();
                SqlCommand cmd = new SqlCommand
                {
                    CommandText = sqlstr,
                    CommandType = CommandType.StoredProcedure
                };
                cmd.Parameters.AddRange(op);
                cmd.Connection = DBConn;
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter
                {
                    SelectCommand = cmd
                };
                da.Fill(dt);
                da.Dispose();
                da = null;
                cmd.Dispose();
                cmd = null;
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecBatchInOneSqlstrTrans(string sqlstr, List<SqlParameter[]> pList)
        {
            try
            {
                if (string.IsNullOrEmpty(sqlstr))
                    throw new Exception("命令不能为空！");
                if (null == pList || pList.Count < 1)
                    throw new Exception("参数不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, DBConn)
                {
                    CommandType = CommandType.Text,
                    CommandText = sqlstr,
                    Transaction = DBConn.BeginTransaction()
                };
                for (int i = 0; i < pList.Count; i++)
                {
                    try
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(pList[i]);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        cmd.Transaction.Rollback();
                        throw;
                    }
                }
                cmd.Transaction.Commit();
                cmd.Dispose();
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecBatchTrans(List<SqlDbBatchModel> sdm)
        {
            try
            {
                if (sdm == null | sdm.Count < 1)
                    throw new Exception("参数不能为空！");
                OpenDB();
                SqlCommand cmd = new SqlCommand
                {
                    Connection = DBConn,
                    CommandType = CommandType.Text
                };
                foreach (var item in sdm)
                {
                    cmd.CommandText = item.SqlStr;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(item.Param);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        cmd.Transaction.Rollback();
                        throw;
                    }
                }
                cmd.Transaction.Commit();
                cmd.Dispose();
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable QuerySP(string sqlstr)
        {
            try
            {
                OpenDB();
                SqlDataAdapter da = new SqlDataAdapter(sqlstr, DBConn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public DataTable QuerySP(string sqlstr, SqlParameter[] op)
        {
            try
            {
                CheckParameterNull(op);
                OpenDB();
                SqlDataAdapter da = new SqlDataAdapter(sqlstr, DBConn);
                da.SelectCommand.CommandText = sqlstr;
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddRange(op);
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                CloseDB();
                return dt;
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecSP(string sqlstr)
        {
            try
            {
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, DBConn)
                {
                    CommandType = CommandType.StoredProcedure
                };
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        public void ExecSP(string sqlstr, SqlParameter[] op)
        {
            try
            {
                CheckParameterNull(op);
                OpenDB();
                SqlCommand cmd = new SqlCommand(sqlstr, DBConn)
                {
                    CommandType = CommandType.StoredProcedure
                };
                cmd.Parameters.AddRange(op);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                CloseDB();
            }
            catch (Exception)
            {
                CloseDB();
                throw;
            }
        }
        #region 检查parameter空值
        private void CheckParameterNull(SqlParameter[] op)
        {
            try
            {
                foreach (var item in op)
                {
                    if (item.Value == null)
                        item.Value = DBNull.Value;
                    else if (item.SqlDbType == SqlDbType.DateTime && string.IsNullOrEmpty(item.Value.ToString()))
                        item.Value = DBNull.Value;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion
    }
}
